Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    79

    Unanswered: Table using Index

    How to create table in oracle that should be made to use an index..
    Thanks!

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    1. create table

    2. create index for colums that you will be using in where clauses in queries against this table

    Go home and take a nap.
    My way or the highway. Yeah

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    and more....

    Use CBO and have the latest table statistics gathered.


    HTH,

    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Feb 2004
    Posts
    79
    sample syntax for this pelase...
    thanks!

  5. #5
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Actually RBO is more indiscriminant in using indexes.
    CBO is smarta' and it might ignore a nonselective index if
    cost of accessing index + cost of accessing table > full table scan
    unless it is a bitmapped index on a nonselective colum then the cost is sqewed towards the index again

    If you are set on using an index you can supply a hint to the optimizer

    select /*+ INDEX(table_name index_name)*/
    run_id, instance
    from...

    Code:
    drop table gcmdb_messages cascade constraints; 
     
    create table gcmdb_messages ( 
    run_id number not null, 
    instance varchar2(20), 
    projectidint varchar2(20), 
    timestamp date not null, 
    text varchar2(200) not null)
    tablespace gcmdb_data;
     
     
    create index gcmdb_messages_indx1
    on 
    "gcmdb".gcmdb_messages(run_id,instance, projectidint)
    tablespace gcmdb_index; 
     
    
    Last edited by Nocopy; 05-05-04 at 12:10.
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •