| points to ponder while having Index against any column [message #465858] |
Thu, 15 July 2010 15:18  |
khurramforum
Messages: 1
Registered: July 2010
Location: Lahore, Pakistan
|
Junior Member |
 
|
|
what are the points to ponder while creating any index against any column.
I have a query
set autotrace traceonly explain;
SELECT SEQ_NUM, CLINICAL_VISIT_SEQ_NUM, PROCEDURE_LIST_SEQ_NUM,SHORT_NAME, DESCRIPTION,CPT_CODE, DETAIL, SORT_ORDER,SIGNED_OFF, EMR_CHARGE_SEQ_NUM , ICD_CODE1, ICD_CODE2, ICD_CODE3, ICD_CODE4 , UNIT, MODIFIER_CODE1, MODIFIER_CODE2, MODIFIER_CODE3, MODIFIER_CODE4, MC1_REQUIRED, MC2_REQUIRED, MC3_REQUIRED, MC4_REQUIRED , ENTERED_BY, ENTRY_DATE,PATIENT_SEQ_NUM,COMMENTS FROM V_CLINICAL_VISIT_PLAN where PATIENT_SEQ_NUM = 10002
and its trace is:
no rows selected.
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2 Card=1 Bytes=4 K)
1 NESTED LOOPS (Cost=2 Card=1 Bytes=4 K)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=4 K)
3 2 TABLE ACCESS FULL SEQUEL1.CLINICAL_VISIT_PLAN (Cost=2 Card=1 Bytes=4 K)
4 2 TABLE ACCESS BY INDEX ROWID SEQUEL1.PROCEDURE_LIST (Cost=0 Card=1 Bytes=52)
5 4 INDEX UNIQUE SCAN SEQUEL1.PK_PROCEDURE_LIST (Cost=0 Card=1)
6 1 INDEX UNIQUE SCAN SEQUEL1.UK_CLIN_VISIT_SEQ_PAT_SEQ_NUM (Cost=0 Card=1 Bytes=11)
it is showing Table Access full Sequel1.clinical_visit_plan.
Now tell me is it necessary to have a index against that.
|
|
|
|
| Re: points to ponder while having Index against any column [message #465862 is a reply to message #465858] |
Thu, 15 July 2010 17:15  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Against what exactly? We have no idea what columns in that table are referenced or how they are referenced - you'd need to show use the view query for that.
2) The explain shows that there is little or no data in the tables. If that's normal then an index will make no difference.
However if you normally have a lot more data than that then it'd be a good idea to populate your tables with a more representative amount of data and re-run the explain plan.
Explain plans can change as you add more data.
|
|
|
|
| Re: points to ponder while having Index against any column [message #465864 is a reply to message #465862] |
Thu, 15 July 2010 18:01 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Points to ponder? Hmm... Pretty wide open question there. Well... let's have a stab at it anyway.
What to index? For non-warehouse databases index the following:
Primary Key
Unique Key
Foreign Key
Additional Columns that will support targeted performance situtations
Two additional things to keep in mind:
"DATA" columns can be added to the end of any index in order to avoid table access by rowid.
Constraints and Indexes are not the same thing. Constraints and Indexes no longer need the same UNIQUENESS nor identical columns.
After a while, if you understand the ramifications of doing so, you can remove indexes off Foreign keys that provide little value. But again you must understand the ramifications of foreign keys not supported by some index.
This is a start. Since your question seems very broad, I can only offer a simple broad statement.
Here is an example of the difference between constraints and indexes:
create table emp (emp_id number not null,emp_name varchar2(30) not null,salary number)
/
create index emp_i1 on emp (emp_id,salary)
/
alter table emp add constraint emp_pk primary key (emp_id)
/
SQL> l
1 SELECT constraint_name,table_name,constraint_type,index_name
2 from user_constraints
3 where table_Name = 'EMP'
4* and constraint_type = 'P'
SQL> /
CONSTRAINT_NAME TABLE_NAME C INDEX_NAME
------------------------------ ------------------------------ - ------------------------
EMP_PK EMP P EMP_I1
1 row selected.
Notice that the index will support the primary key in currently supported versions of Oracle, even though the columns list is not the same and the uniqueness is not the same.
Kevin
|
|
|
|
| Re: points to ponder while having Index against any column [message #466312 is a reply to message #465858] |
Mon, 19 July 2010 01:10 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
The line
TABLE ACCESS FULL SEQUEL1.CLINICAL_VISIT_PLAN (Cost=2 Card=1 Bytes=4 K)
shows that Oracle expects to be retrieving only 1 row from the table.
If the table is really larger than that, your first action would be to check the statistics on the tables and indexes.
What Table(Column) is Index UK_CLIN_VISIT_SEQ_PAT_SEQ_NUM created on ?
V_CLINICAL_VISIT_PLAN is obviously a View. Knowing the definition of the View would help.
Hemant K Chitale
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
