question about indexes

question about indexes

Post by Ty O'Kell » Sun, 31 Dec 1899 09:00:00



hello all,

i have a question about how indexes work.  lets say i have a table
called transaction with fields:

transaction     (primary key)
costctr
chart
account
subacct
amount

there is already an index called transactioni1 created on the following
columns (it is a foreign key):

costctr
chart
account
subacct

my question is this: if i do a select on costctr and account will oracle
make use of the existing index or should i create a new index made up of
the two columns being queried on?

thanks for the help.

ty

--
Ty O'Kelly

 
 
 

question about indexes

Post by Connor McDonal » Sun, 31 Dec 1899 09:00:00



> hello all,

> i have a question about how indexes work.  lets say i have a table
> called transaction with fields:

> transaction     (primary key)
> costctr
> chart
> account
> subacct
> amount

> there is already an index called transactioni1 created on the following
> columns (it is a foreign key):

> costctr
> chart
> account
> subacct

> my question is this: if i do a select on costctr and account will oracle
> make use of the existing index or should i create a new index made up of
> the two columns being queried on?

> thanks for the help.

> ty

> --
> Ty O'Kelly


Oracle can use leading columns in index selection, so it may choose to
take advantage of the costctr (dependent on its cardinality)...

HTH
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"

"Some days you're the pigeon, and some days you're the statue."

 
 
 

1. Index question (best index structures for a table)

I'm looking more for a concept here than an actual specific example.
Lets say I have 2 tables, doc_hdr and doc_line.  

CREATE TABLE [dbo].[doc_hdr] (
        [doc_no] [int] NOT NULL ,
        [info] [varchar] (255) COLLATE French_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[doc_line] (
        [doc_no] [int] NOT NULL ,
        [line_no] [int] NOT NULL
) ON [PRIMARY]

The line table will always be linked to doc_hdr by doc_line.doc_no =
doc_hdr.doc_no

If I do most of my retrieves by doc_no, what sort of indexes should I
have on these tables?  More specifically, should the clustered index
on doc_line be only doc_no, or should it include line_no?

I know this is a vague example at best, but hopefully it's enough info
to get the idea across...

2. sql 2000

3. DTS newbie question regarding indexes

4. Problem with dllhost when Replicating CE SQL Server with SQL Server 2000

5. Question on Index

6. ]]]]]]

7. Question on indexing column(s) within a table

8. JDBC and Sybase 11.0.3 and metadata?

9. Question regarding Indexing

10. Question on Indexes

11. question on indexes

12. Question about indexes and performance.