Re: SQL COMMAND

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Wed, 18 Dec 2002 09:58:18 -0600
Message-ID: <k5610vofk9dqdj7u0d1bnh03o3p2jdoce5@4ax.com>

The use of NEVER is never wise ( even this use of it):

You can create function-based indexes ( with 8i and above) so you CAN use to_date ( and other functions ) without full table scans.

Using TRUNC(creationdate) = to_date('20021201','YYYYMMDD') will get all data created on that day regardless of time. Using TRUNC is the recommended way to do date comparison if the time part is immaterial and the data is in a DATE field.. Otherwise, the TO_DATE(datelikefieldbutactuallycharacterdata,'MMDDYYYY') < TO_DATE('12012002','MMDDYYYY') with a function-based index on to_date(creationdate) will work.

NOTE: The following restrictions apply to Function-based indexes:

---

( From http://tahiti.oracle.com )
Table owners should have EXECUTE privileges on the functions used in function-based indexes. Also, because a function-based index depends upon any function it is using, it can be invalidated when a function changes. You can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled if the function is valid. The ALTER INDEX...DISABLE statement allows you to disable the use of a function-based index. You might want to do this if you are working on the body of the function.

For the creation of a function-based index in your own schema, you must be granted the CREATE INDEX and QUERY REWRITE system privileges. To create the index in another schema or on another schema's tables, you must have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges.

You must have the following initialization parameters defined to create a function-based index:

QUERY_REWRITE_INTEGRITY must be set to TRUSTED

QUERY_REWRITE_ENABLED must be set to TRUE

COMPATIBLE must set to 8.1.0.0.0 or a greater value

Additionally, to use a function-based index:

The table must be analyzed after the index is created.

The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

---

Billy Verreynne <vslabs_at_onwe.co.za> wrote:

>Dominique PREVOT wrote:
>
>> select * from table where (TO_DATE(CreationDate) < 2002/12/01);
> ^^^^^^^^^^^^^^^^^^^^^^
>
>That is bad. You should never use a function on column in a WHERE clause,
>unless there is no other way.
>
>This will result in Oracle not being to use the index on that column. In
>your example above, it will cause Oracle to result to a full table scan.
>
>If you need to perform a conversion in the WHERE clause, do it on the
>literals and not the column, i.e.
>
>SELECT
> *
>FROM my_table
>WHERE creation_date < TO_DATE('2002/12/01','yyyy/mm/dd')
>
>Also keep in mind that if time plays a role, you must be careful as the
>following SQL will _not_ give you all the rows for the 1st of Dec'02:
>
>SELECT
> *
>FROM my_table
>WHERE creation_date = TO_DATE('2002/12/01','yyyy/mm/dd')
>
>What it will give you is all the rows created at time 00:00:00 on the 1st of
>Dec.
>
>If you want all the rows created on that day, you need to do this:
>
>SELECT
> *
>FROM my_table
>WHERE creation_date BETWEEN
> TO_DATE('2002/12/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
> AND
> TO_DATE('2002/12/01 23:59:59','yyyy/mm/dd hh24:mi:ss')
>
>Last comment. Do not use column names like "creationdate". That is not
>readable. You may write it as "CreationDate" in your code and make it
>readable in your code.. Oracle does not and cares not how you write it in
>your code.
>
>Oracle displays the column name as "creationdate". Thus, rather use the
>underscore to make your column names readable, i.e. "creation_date".
Received on Wed Dec 18 2002 - 09:58:18 CST