Dec 21, 2011

NOT IN / NOT EXISTS



 It is recommend to use NOT EXIST instead of NOT IN ; do you know/thought  why ?  ! .




You dont see any differences here because the tables are small with less data and also there will not be any difference in the explain plan too.For very large tables performance improvement are seen by using NOT EXISTS instead  of  NOT IN ; so test it and see whats the difference .


  EXAMPLE

   create table oracle4u ( orclid number(10), constraint pk primary key(orclid) ,s_name varchar2(100) );


   create table "oracle4u.com" ( orcl4id number(10), constraint opp primary key(orcl4id) ,s_name varchar2(100) );


   insert into oracle4u select rownum,OBJECT_NAME  from SYS.ALL_OBJECTS;


   insert into oracle4ucom select rownum,TABLE_NAME  from SYS.ALL_tables union select null,'TEST'  from dual

commit;
-------------------


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from oracle4ucom fg)


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from   oracle4ucom fg where fg.orcl4id is not null)


select * from oracle4u pp where  not exists  (select fg.orcl4id  from oracle4ucom fg where pp.orclid=fg.orcl4id )


select *  from oracle4u pp  Left Outer JOIN oracle4ucom fg ON pp.orclid=fg.orcl4id  WHERE fg.orcl4id IS NULL


Dec 3, 2011

ORA-20229: Workspace Manager Error

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1



when this error occurs


delete the GOLF_YARD_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

Even if  its not working !

Then check whether a trigger exist on GOLF_YARD table or GOLF_YARD view ie : ( when
disabling GOLF_YARD that trigger may not be dropped) .That trigger is now restricting you to version enable your table GOLF_YARD drop the trigger/triggers from GOLF_YARD

and then execute


EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

it will works sure

---------


Nov 20, 2011

Outer Join tends to Complex

  Left outer join retreives all rows in the table1 which is left table ;even if there is no match with table2.


 Right outer join retreives all rows in the table1 which is right table ;even if there is no match with table2.
 
Example
 
CREATE TABLE DEPT_MASTER
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);
CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

ALTER TABLE USER_DETAILS ADD ( CONSTRAINT FK_USER_DETAILS_DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT_MASTER (DEPT_ID));

Insert some data in both tables.

Old Fashion Left Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC  from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;
New  Fashioned  Left Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d left outer join dept_master dd on D.DEPT_ID=DD.DEPT_ID order by 1;

Old Fashion Right Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID   order by 1;

New  Fashioned  Right Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d right outer join dept_master dd on d.DEPT_ID=DD.DEPT_ID order by 1;

------------------------------------------------------------------

Here the scope of this article is not meant to describe this in detail , only to analyze in detail about the Old fashioned outer join it becomes complex and complicated , we can just have a look on it.

Left outer join can be written as below in 3 ways

a) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,left table and (+) outer notation in left to right table column

b) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,right table and (+) outer notation in left to left table column

c) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;-- left outer join (actual) ,left table and (+) outer notation in right to right table column

 
Right outer join can be written as below in 3 ways
 

a) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID order by 1;-- right outer join (actual) right table and (+) outer notation in left to left table column

b) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

c) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

Normal Join


1) select d.user_id,DD.DEPT_DESC from user_details d  join dept_master dd on d.dept_id=dd.dept_id;

2) select d.user_id,DD.DEPT_DESC from user_details d natural join dept_master dd ;

3) select d.user_id,DD.DEPT_DESC from user_details d join dept_master dd using(DEPT_ID) ;

-------------

Nov 14, 2011

Oracle WorkspaceManager ErrorC0des



 
exec dbms_wm.enableversioning('TABLEA');

ORA-20129: table 'TABLEA' does not exist
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version enabling  


----------------------------------------------------------------


exec dbms_wm.disableversioning('TABLEA');

ORA-20132: table 'TABLEA' is not version enabled
ORA-06512: at "WMSYS.LT", line 9355
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version disabling

---------------------------

 exec dbms_wm.enableversioning('USER_RECORDSS_ON_01_SEP_2011')

ORA-20136: table names are limited to 25 characters
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

In an Oracle Workspace Manager only 25 characters named tables are allowed to version enbale


 --------------------

Nov 1, 2011

ORA-20061 ,ORA-00604 and ORA-2017



ORA-00604: error occurred at recursive SQL level 1
ORA-20061: versioned objects have to be version disabled before being dropped
ORA-06512: at "WMSYS.WM_ERROR", line 338
ORA-06512: at "WMSYS.WM_ERROR", line 346
ORA-06512: at "WMSYS.NO_VM_DROP_PROC", line 42

One scenario where  this error is , when you try to drop TESTVERSION_LT  table

If this error is occuring and not able to drop the 'TESTVERSION_LT' table and also not able to version disable the 'TESTVERSION'

FIRST take the backup of TESTVERSION table as
create table TESTVERSION_back as select * from TESTVERSION 
 and create all the indexes on that TESTVERSION_back table
.SECOND try to delete the entry of that table   from WM$VERSIONED_TABLES table and the try to drop VIEW  TESTVERSION AND TESTVERSION_LT  TABLE


------------------------------------------------------------------------------------------------------------

ORA-00604: error occurred at recursive SQL level 1
ORA-20171: WM error: Versioned objects cannot be altered.


alter table TESTVERSION_lt add VERSION INTEGER


FIRST take the backup of TESTVERSION table as create table TESTVERSION_back as select * from TESTVERSION and create all the indexes on that TESTVERSION_back table

SECOND try to delete the entry of that table for example from WM$VERSIONED_TABLES table and the try to drop VIEW TESTVERSION AND TESTVERSION_LT TABLE

-----------------

Compressed Tables Cannot Versiondisable


create table testversion ( pkl number primary key,s_var varchar2(10));

ALTER TABLE TESTVERSION CACHE COMPRESS  PARALLEL ( DEGREE Default INSTANCES Default );

exec dbms_wm.enableversioning('TESTVERSION');

exec dbms_wm.disableversioning('TESTVERSION');

ORA-20231: 'TESTVERSION'  failed during DisableVersioning. Error:
ORA-39726: unsupported add/drop column operation on compressed tables .


  This error occurs when we try to version disable a  compressed table ;its  because during version enabling a compressed table   four columns  VERSION, NEXTVER, DELSTATUS, LTLOCK are not created

SO THERE IS NO USE TO VERSION ENABLE A COMPRESSED TABLE BECAUSE FOLLOWING 
VERSION, NEXTVER, DELSTATUS, LTLOCK COLUMNS ARE NOT CREATED WHICH ARE THE NUCLUES OF THE VERSIONING 


WHEN A COMPRESSED TABLE IS VERSION ENABLED ONLY  A VIEW NAMED 'TESTVERSION' IS CREATED .NO TRIGGERS AND OTHER VIEWS ARE CREATED


WHEN A COMPRESSED TABLE IS TRIED TO VERSION DISABLED ITS STATE IS CHANGED TO 'DV' .EVEN THOUGHT IT THROWS ERROR

CHECK THE BELOW QUERY FOR VERSIONED STATUS OF A TABLE

SELECT DISABLING_VER,UNDO_CODE FROM WMSYS.WM$VERSIONED_TABLES WHERE OWNER='SCHEMAMERGE' AND TABLE_NAME='TESTVERSION' ;

AND IF YOU CHECK IN UNDO_CODE COLUMN OF THIS WMSYS.WM$VERSIONED_TABLES TABLE YOU CAN GET WHY VERSION DISABLE OF THE COMPRESSED TABLE DEOSNT WORK

--------------------

Oct 27, 2011

Tablespace Data mining

    Tablespace Data mining
  -----------------------------
    
      Let me call some of my findings as Oracle Tablespace Data minig some I dont know really what to name this some may call it as Tablespace cracking or hacking but I dont think so ;because what i done is just some analysis with  my current Database and its . DBF and .LOG file so we can call Tablespace Data-mining .


Note : When you are experimenting try to copy .DBF and .LOG file and experiment on the copied file .Most probably you may more details other than mentioned here .
Do experiment
.This is for non-encrypted tablespace


        In Redo Tablespace it records the changes , If you open the RED.LOG file in a text editor you will get dbms_job's 'WHAT' details if a procedure or function is given if query is given we will not get the query detail .Then NLS_LANGUAGE parameter can be searched then search for MAXVALUE keyword

If you open the tablespace you created for a particular schema or Database you will able to see what all data are stored in that DB in that tablespace

Aug 16, 2011

Oracle Exdata



Exdata is a hardware machine which runs oracle database; supports OLTP, Database
Exologic is a hardware machine for application and primarily for cloud computing
Exadata X2-2 Hardware Architecture (FULL Rack)
DBA's has to think about storage server, DB server, storage connecting devices, fiber channel connectivity, Network components, OS, storage drives this all includes in Exdata

In Storage Grid of Exdata it contains 2 Xenon CPU with computing power which take care of processing inside the storages 
Flash is the key performance of Exdata machine. Its a kind of h/w in a pci slot where all your data is stored intermediately between db server and storage server. 
InfiniBand Networks (fiber channel has only 10gb/s) which is also a key performance of Exdata ;which used to transfer data between db server to storage which has high bandwidth

In Exdata is a preconfigured plug and play software .

Key Performance of Exdata
1)      Intelligent Storage Grid

2)      Hybrid Columnar Compression will compress 10 times the data

3)      Smart Flash Cache (patented product of Oracle) different from SSD; it’s a second SGA at storage level which also contains algorithm which drives the flash cache.



In Traditional San if we increase the storage it will not increase the db performance because the spindles of hardware will perform as usual to read from storage
In Exdata there storage is intelligent.  (www.tpc.org to get which is the fastest machine)
ROW level locking
-----------------
it means any moment of time any person can do DML at a moment of time so entire table is locked ,so oracle introduced rowlevel locking mechanism which makes oracle faster
Rowlevel is managed at block level ultimate storage element of ur row.
RMAN is the backup in exdata



TIMESTAMP Datatypes


Once the database time zone is set (with ALTER DATABASE SET TIME_ZONE command) and you have defined single table with a TSWLTZ column in the database, you're locked to that time zone - Oracle won't let you change the database time zone because this will implicitly alter all TSWLTZ columns data - remember that they only hold the offset from the database time zone? If you will change the time zone, TSWLTZ will yeld different timestamps because it will add or substract the offset from new database time zone, not the one that was in effect when you created the row, and this will result in wrong data being returned. So if you attempt to alter the database time zone with at least one column of TSWLTZ type defined in the database, Oracle will throw this rather misguiding error.
Worse yet, TSWLTZ data type is not suited well for distributed systems where several databases around the world serve the application: each database will probably have its own time zone and TSWLTZ data in these databases will not be interchangeable between them. TSWTZ is free from this effect, because original time zone is always known. But then again, you certainly would want to present that data to users residing in different time zones and you would want to show what time it was in THEIR time zone, won't you? Oracle knew about that, and they extended the Datetime Expression syntax with AT clause to allow you to convert TSWTZ to different time zones easily:


--------------------------------------------------------------------
 select * from V$TIMEZONE_NAMES;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM') "US/Eastern",
TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR') AT TIME ZONE 'US/Pacific','YYYY-MM-DD HH24:MI TZH:TZM') "US/Pacific" FROM DUAL;
SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 Europe/Moscow','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM')"Daylight" FROM SYS.DUAL;

select SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') kl,SYS_EXTRACT_UTC(SYSTIMESTAMP) sy , CURRENT_TIMESTAMP,  DBTIMEZONE , localtimestamp ,extract(hour from systimestamp),systimestamp,to_timestamp_tz('10/07/2004', 'DD/MM/YYYY') now,systimestamp(2) ,SESSIONTIMEZONE from dual;


.

Aug 3, 2011

Spatial Error Related with SDO_NN

SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341



When executing this sql  following error occurs .

ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9

We can resolve this error by removing  SDO_NN_DISTANCE ancillary operator from the query but what to do when we require distance what I have noted   is that it is due to non-spatial column which is in where clause is not indexed .When you create a index for that non-spatial column the query will execute

SQL > create index idx on golf_boundaries(gid) ;


SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341 ;


cool
-------




Jul 28, 2011

DBMS_JOB VS DBMS_SCHEDULER

- logging of job runs (job history) (maintain history and a copy of the log file in the database)
- simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
- running of jobs outside of the database on the operating system (can schedule O/S scripts)
- resource management between different classes of jobs
- use of job arguments including passing of objects into stored procedures
- privilege-based security model for jobs
- naming of jobs and comments in jobs
- stored, reusable schedules
- dependencies between job units (10gR2 and up)
- scheduling based on financial calendars and fiscal quarters (10gR2 and up)
- event based jobs which run when an event is received (10gR2 and up)
- running of jobs on remote machines (11gR1 and up)
- e-mail notifications on job events of interest (10gR2 and up)
- starting a job based on arrival of a file (10gR2 and up)

dbms_scheduler has 200 percent more functionality than dbms_job and it makes the windows scheduler and cron look very silly.

Jul 14, 2011

UNDERSTANDING EXPLAIN PLAN 6


 Miscellaneous Operations
FOR UPDATE
Locks returned rows as a result of the FOR UPDATE clause.
FOR UPDATE places a row-level lock on all the rows that can be retrieved from the SELECT statement.
Using FOR UPDATE allows you to use the WHERE CURRENT OF clause in INSERT, UPDATE, and DELETE commands. A COMMIT will invalidate the cursor, so you will need to reissue the SELECT FOR UPDATE after every commit.

Example

select Name, City, State from COMPANY where City > ‘Roanoke’ and Active_Flag = ‘Y’
for update of Name;

Execution Plan

FOR UPDATE
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY that satisfy the limiting condition on the City value (City > ‘Roanoke’). The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. The Active_Flag=‘Y’ criteria is implicitly applied during the TABLE ACCESS BY ROWID operation. The FOR UPDATE operation is then applied to give the user row-level locks on each row returned from the query.
FILTER
Eliminates rows from a result set not matching the selection criteria.
FILTER performs a WHERE clause condition when no index can be used to assist in the evaluation. Unfortunately, the FILTER operation is sometimes implicit. Any FILTER condition that is applied when performing a table access (such as during a TABLE ACCESS BY ROWID) does not show up in the plan. When FILTER shows up in an Explain plan, it usually the result of a missing index or the disabling of an existing index.
The FILTER operation was in a prior example—the CONNECT BY operation’s example. In the query shown in the following listing, the WHERE criteria on the State column is not applied until after the CONNECT BY hierarchy has completed; the resulting rows are filtered to determine which meet the specified State criteria.

Example

select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID start with Company_ID = 1;

Execution Plan

FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT

Interpreting the Execution Plan

The plan shows that first the COMPANY_PK index is being used to find the root node (Company_ID = 1), then the index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_IDs is complete, the FILTER operation—the WHERE clause related to the State value—is applied. The query does not use the index on the State column, although it is available and the column is used in the WHERE clause.
 REMOTE
Accesses an external database through a database link.
REMOTE sends a SQL statement to be executed at a remote node via a database link. The syntax of the SQL statement sent to the remote node is shown in the Other column of PLAN_TABLE.

Example

Since REMOTE requires a remote database access, a database link will be created for this example. The database link connects to the Hobbes account in the database that is identified via the ‘test’ service name in the local tnsnames.ora file.
create database link REMOTE1 connect to hobbes identified by tiger using ‘test’;
In the example query, a local COMPANY is joined to a remote SALES via a NESTED LOOPS join.

Example

select COMPANY.Name from COMPANY, SALES@REMOTE1 where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID = 3 and SALES.Sales_Total > 1000;

Execution Plan

NESTED LOOPS
REMOTE
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK
For the step with the REMOTE operation, you can query PLAN_TABLE for the syntax of the query sent to the remote node:
select Other from PLAN_TABLE where Operation = ‘REMOTE’;
The value of the Other column for this example is:
SELECT "COMPANY_ID","PERIOD_ID","SALES_TOTAL"
FROM "SALES" SALES WHERE "SALES_TOTAL">1000 AND "PERIOD_ID"=3

Interpreting the Execution Plan

The Explain Plan shows that the remote SALES table is used as the driving table for the NESTED LOOPS join (see the NESTED LOOPS operation for a brief discussion of driving tables). The text in the PLAN_TABLE. The Other column shows the query that is executed in the remote database. For each Company_ID value returned by the query of the remote SALES table, the COMPANY_PK index will be checked to see if a matching Company_ID value exists in the COMPANY table. When a match exists, that row is returned to the user by using the NESTED LOOPS operation.
FIRST ROW
Retrieves the first row of a query.
FIRST ROWS uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).
The following statement changes the goal of the cost-based optimizer for your session to best response time:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SEQUENCE
Accesses an Oracle sequence generator to obtain a unique sequence number
SEQUENCE is used when accessing a database sequence via the NextVal and CurrVal pseudo-columns.

Example

Since the SEQUENCE operation requires a sequence to exist, a sequence named COMPANY_ID_SEQ will be created.
create sequence COMPANY_ID_SEQ
start with 1 increment by 1;
In the example query, the next value is selected from the sequence by selecting the NextVal pseudo-column from DUAL.
select COMPANY_ID_SEQ.NextVal  from DUAL;

Execution Plan

SEQUENCE COMPANY_ID_SEQ
TABLE ACCESS FULL DUAL

Interpreting the Execution Plan

The Execution Plan shows that the DUAL table (comprising 1 row, and owned by SYS) is scanned. The COMPANY_ID_SEQ sequence is used to generate the value of the NextVal pseudo-column for the returned row, using the SEQUENCE operation.
INLIST ITERATOR
Performs the next operation once for each value in an IN list predicate.
An INLIST ITERATOR operation appears in the Execution Plan output if an index implements an INLIST predicate.

Example

SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);

Execution Plan

Operation
Options
Object_name
SELECT STATEMENT


INLIST ITERATOR


TABLE ACCESS
BY ROWID
EMP
INDEX
RANGE SCAN
EMP_EMPNO
The INLIST ITERATOR operation iterates over the operation below it for each value in the IN-list predicate. For partitioned tables and indexes, the three possible types of INLIST columns are:
·         Index column
·         Index and partition column
·         Partition column

Index Column

When the INLIST column empno is an index column but not a partition column, then the plan is as follows (the INLIST operator appears above the table operation but below the partition operation):
Operation
Options
Object_name
Partition Start
Partition Stop
SELECT STATEMENT




PARTITION
INLIST

KEY(INLIST)
KEY(INLIST)
INLIST ITERATOR




TABLE ACCESS
BY ROWID
EMP
KEY(INLIST)
KEY(INLIST)
INDEX
RANGE SCAN
EMP_EMPNO
KEY(INLIST)
KEY(INLIST)
The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate appears on the index start/stop keys.

Index and Partition Columns

When empno is an indexed and a partition column, the Explain plan contains an INLIST ITERATOR operation above the partition operation:
Operation
Options
Object_name
Partition Start
Partition Stop
SELECT STATEMENT




INLIST ITERATOR




PARTITION
ITERATOR

KEY(INLIST)
KEY(INLIST)
TABLE ACCESS
BY ROWID
EMP
KEY(INLIST)
KEY(INLIST)
INDEX
RANGE SCAN
EMP_EMPNO
KEY(INLIST)
KEY(INLIST)

Partition Column

When empno is a partition column and there are no indexes, then no INLIST ITERATOR operation is allocated:
Operation
Options
Object_name
Partition Start
Partition Stop
SELECT STATEMENT




PARTITION


KEY(INLIST)
KEY(INLIST)
TABLE ACCESS
BY ROWID
EMP
KEY(INLIST)
KEY(INLIST)
INDEX
RANGE SCAN
EMP_EMPNO
KEY(INLIST)
KEY(INLIST)
When emp_empno is a bitmap index, the Execution plan is as follows:
Operation
Options
Object_name
SELECT STATEMENT


INLIST ITERATOR


TABLE ACCESS
BY INDEX ROWID
EMP
BITMAP CONVERSION

TO ROWIDS
BITMAP INDEX
SINGLE VALUE
EMP_EMPNO

LOAD AS SELECT
Denotes a direct path INSERT based on a SELECT statement.
LOAD AS SELECT bypasses the buffer cache when performing a direct path load based on a SELECT statement.
An Execution plan will denote the presence of a direct load operation with the LOAD AS SELECT operation:

Rows
Explain Plan


0
SELECT STATEMENT GOAL: CHOOSE


0
LOAD AS SELECT


0
TABLE ACCESS (FULL) OF 'CUSTOMERS'

FIXED TABLE
Accesses a "fixed" (X$) table.





Fixed tables are those not in Oracle’s data dictionary. FIXED TABLE is normally used to optimize V$ and X$ statements. However, join order for a V$ query will be entirely determined by the order of tables in the FROM clause. Be careful when using FIXED TABLE because:
·         Neither the cost-based optimizer nor the rule-based optimizer recognizes the presence of V$ indexes when determining join order or method.
·         There are never any optimizer statistics held against the V$ or X$ tables and consequently the cost-based optimizer has no information to use to determine the best join order.
FIXED INDEX
Accesses an "index" on "fixed" (X$) table.
Where an index exists on a V$ table, it will normally be used whenever the column is used for an exact lookup. The Explain Plan reveals that this is so through the special access path FIXED INDEX. For instance, the following query uses the SID index on V$SESSION:
select * from v$session where sid=171
Rows
Explain Plan
1
FIXED TABLE FIXED INDEX #1 X$KSUSE
cpu=1 elapsed=1 logical=0 physical=0
Remembering that the "index" is not really an Oracle B-tree index and in has more in common with a hash cluster, it’s not surprising to see that the index is disabled if a range scan is attempted:
select * from v$session where sid<8

Rows
Explain Plan


700
FIXED TABLE FULL X$KSUSE
cpu=19 elapsed=19 logical=0 physical=0

TEMP TABLE GENERATION
Creates a temporary dimension table for dimension tables joined by a Star Transformation.





The TEMP TABLE GENERATION operation creates a temporary dimension table for dimension tables that have been joined by a Star Transformation. For each dimension table in the joined set, TEMP TABLE GENERATION creates a temporary table to replace the table in the Execution Plan. A temporary table is created using two steps: create table and insert each. Since there are at least two tables in a join, this operation creates a minimum of four temporary tables.
COLLECTION ITERATOR
Returns certain values from a collection such as VARRAY and nested table.
The Collection Iterator operation returns certain values from a collection such as VARRAY and nested table.