PLSQL

  Question:  Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table? Answer:   The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. Also see this great script to  display all high water marks . As a table undergoes deletes and updates, rows shrink and table data blocks become empty.  For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 30 seconds to read.  After deleting 900...

  I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query: Item | Year | Month | Amount | New_Amount AAA | 2013 | 01 | 100 | 100 AAA | 2013 | 02 | | 100 AAA | 2013 | 03 | 150 | 150 AAA | 2013 | 04 | 125 | 125 AAA | 2013 | 05 | | 125 AAA | 2013 | 06 | | 125 AAA | 2013 | 07 | | 125 AAA | 2013 | 08 | 175 | 175 I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going...

  Why does the same query takes different amount of time to run?    There are three broad reasons that queries take longer at different times. Either you are getting different performance because the system is under a different sort of load, you are getting different performance because of data volume changes, or you are getting different performance because you are getting different query plans. Different Data Volume When you generate your initial timings, are you using data volumes that are similar to the volumes that your query will encounter when it is actually run? If you test a query on the first of the month and that query is getting all the data for the current month and performing a bunch of aggregations, you would expect that the query would get slower and slower over the course of the month because it had to process more and more data. Or you may have a query that runs quickly outside of month-end processing because various staging tables that it depends on o...

 --set SERVEROUTPUT ON -------------------------------FETCHING RECORDS IN SCALAR VARIABLE------------------------------------ DECLARE     v_name VARCHAR2(100); BEGIN     SELECT first_name        INTO v_name     FROM employees     WHERE employee_id=100;          dbms_output.put_line('v_name '||v_name); END; / --------------------------FETCHING RECORDS INTO COLLECTION OBJECT---------------------------------- DECLARE     TYPE lv_name_list_type IS TABLE OF VARCHAR2(100);     lv_name_list lv_name_list_type := lv_name_list_type(); BEGIN     SELECT first_name     BULK COLLECT INTO lv_name_list      FROM EMPLOYEES;          FOR I IN lv_name_list.first..lv_name_list.last     LOOP         dbms_output.put_line(lv_name_list(I));     END LOOP; END; / --------------------EXPLICT CURSOR WITH LOO...

Create 2 views, 1 trigger, 2 stored procedures based on some situation. 1.) Create student  and fee table 2.) generate roll number procedure 3.) Insert Procedure 4.) Trigger to audit updatation of address 5.)  Create view to visualize student paid fee and along with student information. 6.)  Create view for old and new address CREATE TABLE STUDENT_NEW(StudentId VARCHAR2(8), FirstName VARCHAR2(20), LastName VARCHAR2(20), Age NUMBER, Address VARCHAR2(50),  Mobile NUMBER, Email VARCHAR2(100), CONSTRAINT PK_StudentId PRIMARY KEY (StudentId)); --drop table student_new INSERT INTO STUDENT_NEW VALUES('00000001', 'Ravi','Shastri', 26, 'Red Cross Marg, Ulhasnagar, Mumbai', '9112340987','Ravi001@gmail.com') To generate new roll no Insert new student information CREATE OR REPLACE PROCEDURE GENERATE_STUDENT_ID(pStudentId OUT VARCHAR2) AS v_StudentId VARCHAR2(8); BEGIN SELECT MAX(STUDENTID) INTO v_StudentId  FROM STUDENT_NEW; --DBMS_OUTPUT.PUT_LINE(v_...

We have data of attendance('ab'--> absent, null--> present) on different dates(d1, d2..) select id, value from ( select 1 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union all select 2 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union all select 3 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union all select 4 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union all select 5 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual )  UNPIVOT ( value for value_type in(d1,d2,d3,d4,d5) ) Later, you can take the count of it. select id, count(value) from ( select 1 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union all select 2 id, null d1, 'ab' d2, 'ab' d3, 'ab' d4, 'ab' d5 from dual union a...