Home » SQL & PL/SQL » SQL & PL/SQL » indexes
indexes [message #215377] Sun, 21 January 2007 22:38 Go to next message
sajut
Messages: 69
Registered: January 2007
Member
Dear All
I have the following table
ch_siscon_Code varchar2(2)
ch_branch_code varchar2(2)
ch_challan_no varchar2(19),
ch_voucher_no varchar2(19)

column ch_voucher_no can be null for certain rows.
Which is the best way to create an index on this table
ch_siscon_code, ch_branch_code, ch_voucher_no
or
ch_siscon_code, ch_branch_code, nvl(ch_voucher_no,'N')
Re: indexes [message #215417 is a reply to message #215377] Mon, 22 January 2007 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The indexes you need are determined entirely by how you are going to be querying the table.

If you want to look for rows in the table for a given ch_siscon_code, ch_branch_code and ch_voucher_no where the voucher_no is not null, then the first index is fine.
Re: indexes [message #215929 is a reply to message #215417] Wed, 24 January 2007 05:49 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Thanks for the advice
when a date column is also part of the query, say for examle
select ch_cust_code, sum(ch_amount) from challan where ch_siscon_code='04' and ch_branch_code='06' and trunc(Ch_inv_date) between to_Date('01/04/2006','dd/mm/yyyy' and to_date('31/12/2006',dd/mm/yyyy') group by ch_cust_code

how should the index be
is it ch_siscon_code, ch_branch_code, ch_inv_date
or
ch_siscon_code, ch_branch_code, trunc(ch_inv_date)?

also is this be called a function based index
Re: indexes [message #215958 is a reply to message #215929] Wed, 24 January 2007 07:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd only put 'Trunc(date)' in the index if
a) I used trunc(date) in the query, and
b) I'd set the system up to use function based indexes (should have mentioned that in my first post).

In general, just add the entire date field to the index.
Re: indexes [message #215963 is a reply to message #215929] Wed, 24 January 2007 07:49 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And I wouldn't use trunc(date) here anyway. I'd go with:
Ch_inv_date >= to_Date('01/04/2006','dd/mm/yyyy' and
Ch_inv_date <  to_date('01/01/2007',dd/mm/yyyy')
Re: indexes [message #216077 is a reply to message #215963] Wed, 24 January 2007 23:08 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
when ch_inv_date is in yyyymmddhh:mi:ss format ????
Re: indexes [message #216096 is a reply to message #216077] Thu, 25 January 2007 00:25 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Doesn't matter ch_inv_date's format; apply the one you are using: ('yyyymmddhh:mi:ss') instead of the one Joy Division suggested.
Previous Topic: comparing sum values
Next Topic: Databae connecting problem after creating the trigger-reg
Goto Forum:
  


Current Time: Wed Dec 07 18:54:51 CST 2016

Total time taken to generate the page: 0.06102 seconds