| Re: Regarding INDEX [message #2574] |
Mon, 29 July 2002 04:47 |
K.SREENIVASAN
Messages: 110
Registered: January 2001
Location: banglore
|
Senior Member |
|
|
SIR,
FOR EXAMPLE YOU ARE HAVING A TABLE CALLED EMP
THESE TABLE CONTAINS NAME,NO,ADDRES,AGE(TAKE 20 COLUMNS LIKE THIS).INSERT 20,00,000 RECORDS TO THIS TABLE.
THEN RUN THE UTXOLAN.SQL FILE(IT MAY BE IN THE ORACLE_PATHRDBMSADMIN DIRECTORY.IT WILL CREATE PLAN_TABLE IN YOUR CURRENT USER SCHEMA.
THEN
SQL>SET AUTOTRACE ON
SQL)SELECT * FROM EMP WHERE EMPNO=10000;
IN THE ABOVE QUERY,IT WILL LOAD THE FULL TABLE IN TO THE MEMORY AND IT WILL FIND THE 10000 RECORD.
IT WILL TAKE A LONG TIME
INSTEAD OF THAT ONE,WE ARE GOING FOR INDEX.
IF YOU CREATE THE INDEX FOR PARTICULAR COLUMN,THEN INDEX SEGMENT IS CREATED IN YOUR DATABASE AND THE VALUE IN THE COLUMN IS STORED IN THE INDEX SEGMENT WITH ROWID.IF YOU EXECUTE THE QUERY NOW,THEN IT WON'T LOAD FULL TABLE.IT LOAD ONLY INDEX SEGMENT AND YOU CAN GET THE ROW EASILY
SQL>SET AUTOTRACE ON;
SQL>CREATE UNIQUE INDEX EMINDEX ON EMP(EMPNO);
SQL>SELECT * FROM EMP WHERE EMPNO=10000;
COMPARE THE EXPLAIN PLAN AND IT WILL GIVE ANSWER FOR YOU.
K.SREENIVASAN
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
