PL/SQL :: Oracle 10.2.0.4 Index On Timestamp Column Not Used When SYSTIMESTAMP Used
Oct 23, 2012
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg :
select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
View 4 Replies
ADVERTISEMENT
Sep 16, 2012
I have one table with a columntype "timestamp" say xyz is the name of the column.Suppose today is 15th Sept So I want to fetch all those records whose value is between 14th Sept 00:00:00 and 14th Sept 23:59:59.
And date I want to keep this as a variable.
View 5 Replies
View Related
Apr 10, 2013
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
View 24 Replies
View Related
Oct 14, 2012
i am running one query which is here
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.
getting this error.
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
View 19 Replies
View Related
Jun 17, 2012
how to extract DATE from timestamp column
View 2 Replies
View Related
Mar 6, 2012
I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.
View 9 Replies
View Related
Oct 5, 2010
Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.
1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.
2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.
3> Will there be performance impact on an existing application that uses the date field to sort
4> Will DB need more space due to the change
View 3 Replies
View Related
Jun 14, 2012
I need to change systimestamp from +01:00 to +08:00.
SYSTIMESTAMP
14-JUN-12 03.29.47.657901 PM +01:00 =====> I need to change it as +08:00
how can i change it?
I added TZ=SGT in my listener.ora file and bounced the listener. Even though systimestamp is still showing --> +01:00
View 3 Replies
View Related
Oct 26, 2013
I have a staging which connects to RAC clusters via dblink..I have a scenario to collect the hostname,systimestamp whenever i do a query from this rac clusters
So in general below would be cool
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric would work perfect
But on dblink..
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink.is returning local timestamp and hostname which is not intended
So i even tried subquery..
select (select host_name,systimestamp from v$instance),t UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink
this would work in certain case ...But it certain cases it would throw an error saying mutiple rows from asubquery .All ineed is to get hostname,systimestamp append to all sql i fetch from any this rac clusters through this dblinks.
View 8 Replies
View Related
Mar 27, 2013
I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
View 0 Replies
View Related
Jul 23, 2013
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
View 7 Replies
View Related
Nov 2, 2012
I have found there is a wearied of email generated from my database. I have the data base server located in US (estern time) and the sysdate in the database is AUSTRALIA (AEST). But i am working from India (IST).I have one procedure, which will send email to some recipients once the job is done. It is using UTL_SMTP for sending the email from database.
Now problem is, the email i got from this procedure has a different timestamp which is NOT matching to any of the times:
Time in Mail : 2-Nov-2012 9.56 PM
IST Time : 2-Nov-2012 10.56 AM
Easter Time : 2-Nov-2012 01.30 AM
AEST Time : 2-Nov-2012 4.30 PM
View 1 Replies
View Related
Mar 20, 2012
I'm trying to deduct x hours from a timestamp value. Everything works fine if x yields a result that is on the same day as the value I'm deducting it from. However, if it yields the day before then it gives the wrong answer using DML (although in SQL it works fine). Let me explain with an example:
Let's say current_timestamp is 20-MAR-12 09.40.00.000000 +00:00
If I deduct 8 hours from this I get the correct answer: 20-mar-12 01:40:00 (syntax: show current_timestamp-8/24)
If however, I deduct 10 hours from it (which takes it to the day before) I get garbage: 20-mar-12 00:20:00 (syntax: show current_timestamp-10/24). It should say: 19-mar-12 23:40:00
In SQL I get the correct result every time. So, my question is, is this a bug or intended behaviour? If the latter then what is it doing and how can I get it to give the answer I'm looking for?
What I'm trying to do is convert a timestamp from one timezone to another using DML. The NEW_TIME function will only give me what I want if I know whether the "to" timezone is currently in daylight saving or not. eg. Must I use CST or CDT?
The full syntax I'm using to try and achieve my aim is below.
show to_char( to_date( '2012-03-19--15-37-23' 'YYYY-MM-DD--HH24-MI-SS' )-(convert( extchars( tz_offset( 'AMERICA/CHICAGO' ) 3, 1 ) int )/24) 'YYYY-MM-DD--HH24-MI-SS' )
where 2012-03-19--15-37-23 is a user-supplied argument.
View 25 Replies
View Related
Oct 31, 2012
I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:
a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0
First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?
View 1 Replies
View Related
Jan 17, 2011
I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.
SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );
Table created.
SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);
Index created.
SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SQL >
View 6 Replies
View Related
Oct 5, 2013
If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?
View 2 Replies
View Related
Aug 2, 2012
Is the index suggested on the date column.
Here is the nature of the date column in my case:
1. The table populates with 1000's of records every day with date being always incremental (current date).
2. The search criteria from the weba application (ADF) is based on the date, user gives the range.
3. From ADF I am referring to it as sql.Timestamp when building the query.
Does Index suggested on the date column here and if so what type of index ?
View 4 Replies
View Related
Sep 23, 2010
I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.
Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?
View 4 Replies
View Related
Oct 13, 2011
I have a table with, for example, three columns: A, B,C.
I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2
Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?
View 4 Replies
View Related
Jan 24, 2012
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
View 4 Replies
View Related
Apr 15, 2010
I am creating an index in program and then drop the index at the end of the program. Some times due to some problem if the index could not be dropped and the user again runs the program then we get the error
ORA-01408 Index already exist on the column.
how I can get away with this error or how I can check whether the index with the same columns exits prior to creating an index.
Is there any command like
Create or replace index.....
View 6 Replies
View Related
May 17, 2011
what my issue is with this virtual column
CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS
[code]...
Lastly, can an index be created on a virtual column? I am thinking no since it calculated?
View 3 Replies
View Related
Dec 11, 2012
explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.
A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.
View 2 Replies
View Related
Apr 2, 2011
here is my query
SELECT CURRENTSTEP
FROM (SELECT ( WFENTRY.NAME
|| ','
|| CURRENTSTEP.STEP_ID
) AS CURRENTSTEP,
(CASE
WHEN WFENTRY.NAME IN
[Code]...
in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?
here is how I use it
select
sys_audit_id
from
( SELECT
*
FROM
(SELECT
F.FINDING_NUMBER,
[Code]....
I saw adding this as a subquery with the filter and CURRENTSTEP = ? is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?
View 3 Replies
View Related
Feb 20, 2013
Version Info: 11.2 on Solaris 10
I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?
CREATE TABLE sales_dtl
(
txn_id number (9),
salesman_id number(5),
salesman_name varchar2(30),
sales_rgn varchar2(10), -----------------------------> This column needs to be indexed
sales_amount number(10),
sales_date date,
constraint pk_sales_dtl primary key (txn_id)
[code]....
View 9 Replies
View Related
Oct 10, 2013
We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data
SELECT *
FROM address
WHERE address1 = 'a'
[Code]....
View 9 Replies
View Related
Feb 2, 2012
I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.
Question 1:In case of dropping and recreating the index, should the following steps be done:
- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index
Question 2:Is there anything else that should be done when the index is dropped and re-created?
Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?
Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?
View 2 Replies
View Related
Feb 25, 2012
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
View 11 Replies
View Related
Jan 10, 2013
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%'
like 'abc%'
like '&abc'
Will be the corresponding index will be for those cases?
View 3 Replies
View Related
May 18, 2012
I am working with following select clause:
select distinct S.ID ID
from
ods.hso_Scheduled H,
ods.SO_SCHEDULED S
where
S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
and H.ID=S.ID
Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.
I have created following indexes on these tables:
Indexes on SO_SCHEDULED:
Index name Column name
SS_IDX1ID, SO_SUB_ITEM__ID
SS_IDX2INSERTION_DATE
SS_IDX3ID, INSERTION_DATE
SS_IDX4ID, SO_SUB_ITEM__ID, INSERTION_DATE
SO_SCHEDULED_ID_PKID
Indexes on HSO_SCHEDULED:
HSS_IDX1ID, SO_SUB_ITEM__ID, LAST_UPDATING_DATE
HSS_IDX2ID, LAST_UPDATING_DATE
HSS_IDX3ID
My problem is despite of having relevant indexes present, my query is not hitting them and hence the performance is very bad.
Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 574170360
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 814K| 38M| | 9574 (1)| 00:02:15 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 814K| 38M| | 9574 (1)| 00:02:15 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 814K| 38M| 185M| 9574 (1)| 00:02:15 | Q1,02 | PCWP | |
|* 4 | HASH JOIN | | 2653K| 124M| | 9564 (1)| 00:02:14 | Q1,02 | PCWP | |
| 5 | PX JOIN FILTER CREATE| :BF0000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| SO_SCHEDULED | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | P->P | HASH |
| 12 | PX JOIN FILTER USE | :BF0000 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| HSO_SCHEDULED | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("H"."ID"="S"."ID")
9 - filter("S"."INSERTION_DATE">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S"."INSERTION_DATE"<TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
View 6 Replies
View Related
