Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Question Unanswered: Query Operators (performance)

    Hello,

    I have to build Queries on a Oracle VLDB. But at the moment, there is very few data on the tables.

    I need an advice on WHERE Operators ; which SQL will give the best performance with millions of rows?

    WHERE Table.field >= 'A'

    or this one :

    WHERE Table.field BETWEEN 'A' AND '~'

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Operators (performance)

    Presumably there is no data in the table where Table.field > '~', otherwise your queries would not be equivalent.

    In which case, the BETWEEN version is doing a redundant check, i.e. it is testing:

    WHERE Table.field >= 'A'
    AND Table.Field <= '~'

    So if anything, the BETWEEN version will be slower - in practise, probably negigibly so. But it is also less intuitive. So I'd opt for the first (indeed I would not even have contemplated the second!)

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: Query Operators (performance)

    It's no so much the operators you use, but which operators in combination with which optimising methods Oracle can find/use. Your biggest drawback is having little data to play with - access paths can change quite dramatically as the tables get populated and the granularity of indexes change with the data.

    I've seen Oracle running full table scans on <50,000 row tables even though unique (pk) indexes were available. I sat there complaining about the optimiser (CBO) only to discover that such a 'small' table returned a lower overall cost with a full scan rather than with a forced index unique by rowid - but with higher disk reads.

    It is quite difficult to monitor and check access paths without a realistic data set to play with.

Posting Permissions

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