| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: alter session
A copy of this was sent to Detlev Goebel <goebel_at_www.gzs.de>
(if that email address didn't require changing)
On Tue, 03 Nov 1998 11:23:44 +0100, you wrote:
>Hi,
>
>that's all ok thanks.
>My Problem:
>
>I have nearly 100Meg Records and I have to build a group by query by date. The
>stored date is a timestamp with group by I receive
>a group for each second. On monthly base, where this query has to work, I will
>have ~ 10 Meg Records and I have more than one record for each
>second.
>Using to_char to format the date will take hours for the SQL script.
>If I run sqlplus by doing the following:
>alter session set nls_date_format = 'YYYY.MM.DD'
>select count(*), trn_dt from v_trx where trn_dt between
>to_date('19980401','YYYYMMDD') and to_date('19980430','YYYYMMDD')
>group by trn_dt;
>
>will give me the result I expected. For each day a number. The performance of
>this query is ok (1 min). I thought, by running the alter session
>command before the select statement will give me the correct results - but ODBC
>runs the alter session command and the select statement
>is grouped by seconds. That means, that ODBC uses a new connect with each
>statement.
>
I think you are missing something at some stage of your testing here. If I do the following in SQL*Plus (not odbc)
SQL> create table t ( x date );
Table created.
SQL> insert into t values 2 ( to_date( '01-jan-1998 12:01:01', 'dd-mon-yyyy hh24:mi:ss' ) ); 1 row created.
SQL> insert into t values
2 ( to_date( '01-jan-1998 12:01:02', 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
SQL> alter session set nls_date_format = 'YYYY.MM.DD'; Session altered.
SQL> select count(*), x from t group by x;
COUNT(*) X
---------- ----------
1 1998.01.01
1 1998.01.01
SQL> select count(*), to_char(x) from t group by to_char(x);
COUNT(*) TO_CHAR(X)
---------- ----------
2 1998.01.01
You see -- the NLS_DATE_FORMAT will have NO effect on the group by unless to explicity TO_CHAR the date field in the SELECT and GROUP BY statements. As long as you do not to_char the date field in the WHERE CLAUSE, it will still use the index and be fast.
If you do not TO_CHAR the date field then the date field is grouped by the day, month, year, hour, minute and seconds field.
Add the to_char() to the SELECT and GROUP by. In fact -- leave the ALTER SESSION out of the picture all together, run the query as:
select count(*), to_char(trn_dt,'yyyymmdd') from v_trx where trn_dt between to_date('19980401','YYYYMMDD') and to_date('19980430','YYYYMMDD') group by to_char(trn_dt,'yyyymmdd');
>Now I'm looking for a solution to get right values. A new index and/or new
>column is impossible (not enough space).
>
>Thanks again
>
>Detlev
>
>Billy Verreynne schrieb:
>
>> Detlev Goebel wrote in message <363DD1E4.FE221178_at_www.gzs.de>...
>>
>> >we have a connection to oracle 7.3 via ODBC. Now we like to have an
>> >alter session command to the database.
>> >The ODBC driver isn't capabla to provide SQLP Plus Commands to the
>> >server, login.sql works with sqlplus.
>>
>> Yes, if by that you mean SQL*Plus specific commands like DESCR, SET, SPOOL
>> etc. However, the ALTER SESSION is an Oracle SQL command.
>>
>> >Is there any idea to do an alter session command within a profile or
>> >something similar ?
>>
>> ODBC drivers (should?) use the current PC's regional settings to set the
>> date format, currency format etc. If you trace the ODBC calls made (or even
>> better, sniff the ODBC network traffic), you'll see what ODBC does when it
>> connects to a database. (and this can be scary sometimes, especially if you
>> do not use the correct "method" in Visual Basic!)
>>
>> With Oracle SQL you can however change any of the NLS parameters useing the
>> ALTER SESSION SQL command. If you get an error executing this command in VB
>> (or whatever language you're using) then you either have the syntax wrong,
>> or you're attempting to run the SQL command as if it's returning a cursor
>> handle (like with a SELECT command). Simply run the ALTER SESION command
>> using the EXECSQL or whatever method is used to run a DDL command.
>>
>> regards,
>> Billy
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Nov 04 1998 - 07:58:10 CST
 |
 |