Truncate Vs Delete

What is the difference between truncate and delete?

1.  Truncate deletes all records at once unconditionally, whereas delete can delete the records conditionally or unconditionally.
2. Deleted data by truncate command can not be rolled back, whereas deleted data by delete command can be rolled back.
3. Memory will be released after deletion of record by truncate command whereas memory will not be released after deletion of record by deleted command.
4. Truncate is a DDL command, whereas delete is DML command.
5. Trigger does not get fired in case of  TRUNCATE whereas triggers get fired in case of a DELETE command.
6. We can not TRUNCATE a table if table have any foreign key constraint. We need to remove the constraint  then truncate the table and recreate the constraint.

Relational Vs Nested Table

What is the difference between relational table and nested table?

• A relational table can have virtual column, whereas nested table can not have virtual column.
• Primary key, foreign keys are allowed on a relational table, whereas constraints are not allowed on nested table.

ORA-06548: no more rows needed

ERROR: ORA-06548: no more rows needed

CAUSE: The caller of a pipelined function does not  need more rows to be produced by the pipelined function.

ACTION: Catch the NO_DATA_NEEDED exception in an exception handling block.
Catching the NO_DATA_NEEDED exception inside the pipelined function allows the function to perform any clean-up needed after the loop.

Example:
The table function returns 1000 rows, but the client (which communicates using ODBC) only fetches 200 at a time. If all the rows are fetched,
then there is no issue; however, if only a subset are fetched before another command is executed, the exception gets raised.

DECLARE

  --...

BEGIN

 ....

EXCEPTION

WHEN no_data_needed
THEN
RETURN;   --clean up the resource

END;

/

ORA-01008 : Not all variables bound

ERROR: ORA-01008 : Not all variables bound

CAUSE : A SQL statement (or Dynamic SQL ) containing substitution variables was executed without all variables bound.

ACTION: All substitution variables must have a substituted value before the SQL statement is executed.

what to look for to help performance

Identify the solution for what problem by looking the TKPROF.

If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.

If Disk reads are very high: Indexes are not being used or may not exist

If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.

If parse elapse time is high: There may be a problem with the number of open cursors.

If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.

If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

1. SQL Statements: The first part of a TKPROF statement is the SQL  Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics:  It has all the Statistics for this SQL Statements. It has eight columns 

• call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

      Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

• Count number of times each individual activity has been performed on this particular CALL.
• CPU time used by this CALL.
• ELAPSED time for this CALL(includes the CPU time).
• DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
• QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
• CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
• ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of  the last user to parse this statement.   

4. EXPLAIN PLAN : This most useful section of  the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.

ls Command

• The ls is the most widely used command in unix or linux.
• The ls command is a command-line utility for listing the contents of a directory or directories given to it via standard input.

CAST Function

CAST function converts one built-in data type into another built-in data type with the specified precision and length.

Syntax: CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

CAST Converts

Convert a string value to NUMBER leaving 2 digits after the decimal point:

SELECT CAST('345.213' AS NUMBER(5,2)) FROM DUAL;

CAST - Convert and Round Numbers:

SELECT CAST('246.206' AS NUMBER(5,2)) FROM DUAL;

CAST convert the date into a VARCHAR2(30) value:

 SELECT CAST( '22-Aug-2003' AS VARCHAR2(30) ) FROM DUAL;

TRUNC for Datetime

TO_DATE

TO_CHAR for Datetime

MONTHS_BETWEEN

MONTHS_BETWEEN function returns the number of months between two dates.

Syntax: MONTHS_BETWEEN(date_expression1,date_expression2);

Months Between Function Details

MONTHS_BETWEEN function returns an integer number if the days are the same.

SELECT MONTHS_BETWEEN (DATE '2012-02-12', DATE '2012-01-12') FROM DUAL; 

MONTHS_BETWEEN function returns an integer number if the both dates specify the last day of the month.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-01-31') FROM DUAL; 

MONTHS_BETWEEN function returns a decimal number if the days are different.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-02-01') FROM DUAL;

MONTHS_BETWEEN always calculates the fractional part as the difference in days divided by 31

In the example above, the fractional part is calculated as (29 - 1)/31 = 0.903225806, although there are 29 days in February 2012.

You can also see that MONTHS_BETWEEN does not return an integer result if you specify the first and last days of the same month.

EXTRACT Function

EXTRACT function gets the specified part (day, month, year, hours, minutes,second etc.) from a datetime value.

Syntax: EXTRACT(datetime_unit FROM datetime_expression1)

Although DATE contains time part but we can't extract the hour,minute and second from DATE.
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

EXTRACT Function Details

Extract DAY from a specific DATE.

SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual;

Extract MONTH from a specific DATE.

SELECT EXTRACT(MONTH FROM DATE '2011-01-12') FROM dual;

Extract YEAR from a specific DATE.

SELECT EXTRACT(YEAR FROM DATE '2011-01-12') FROM dual;

We can't extract  hour,minute and second from DATE. We will get the error

SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;

HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual;