Date To Timestamp Conversion Of Column With 150 Million Rows?

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


ADVERTISEMENT

SQL & PL/SQL :: Million Of Rows And No Of Column More Than 300

Mar 28, 2011

I huge table with million of rows and no of column more than 300.. is it possible to keep this table in some memory where oracle can access it fast as compare to disk memory.

View 8 Replies View Related

Search Over Nvarchar2 (4) Column In 4 Million Rows?

Apr 29, 2011

have a performance problem with a simple query, for example:

SELECT *
FROM CA_ADDRESS
WHERE address_k1 = 'L163'

I have an index created in column address_k1. CA_ADDRESS is a 3-4 Million Record table.

I need to improve the performance of the query. How I can improve it?

View 3 Replies View Related

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

Apr 25, 2012

trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.

Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.

Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.

View 13 Replies View Related

SQL & PL/SQL :: Query To Get Timestamp From Only One Date Column?

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

Extract Date From Timestamp Column?

Jun 17, 2012

how to extract DATE from timestamp column

View 2 Replies View Related

SQL & PL/SQL :: Joins On Two Tables With 10 Million Rows Each

Jun 11, 2010

We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.

Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.

View 20 Replies View Related

Performance Tuning :: Update Million Rows In One Table With Values From Another Tables?

Feb 15, 2011

I am trying to update a million rows in one table with the values from another tables.

Table being updated CI_ADJ_CHAR column CHAR_VAL_FK1
Table from which values will be used CK_ADJ columns (cx_id, ci_id)

The CI_ADJ_CHAR.CHAR_VAL_FK1 values match CK_ADJ.CX_ID and should be updated with the value CK_ADJ.CI_ID.

The CK_ADJ table has 1.3 million rows and both the columns have indexes defined. Table definitiuon mentioned below

The CI_ADJ_CHAR table has 14 million rows and will update 1 million rows and has an index on the ADJ_ID column but not on the CHAR_VAL_FK1 column.

View 1 Replies View Related

SQL & PL/SQL :: Timestamp To Date

Jul 26, 2011

From ETL to Oracle, I have stored the timestamp in varchar2 as '30-MAY-11 06.30.00.000000 PM'. Now I need to convert this varchar2(timestamp format) to date. I used:

select TRUNC('30-MAY-11 06.30.00.000000 PM','dd/mm/yyyy hh24:mi') from dualBut, it doest work. get date format?

View 7 Replies View Related

Select All Rows And Order By Timestamp

Dec 27, 2006

I have 3 tables that I want to select all rows from and then order by the timestamp of when the row was inserted.

View 7 Replies View Related

SQL & PL/SQL :: Calculating Sum From Duplicate Rows With Timestamp?

Sep 16, 2011

I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data

CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Extracting Date From Timestamp?

Jan 18, 2012

How to extract date from a timestamp data

for example

Input-15/06/2010 05:30:00.000000
output: 15/06/2010

View 3 Replies View Related

SQL & PL/SQL :: Columns Into Rows Conversion

Mar 24, 2011

I have the following query

select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from
(select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a,
(select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b
where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME

This is the output i get :

EV_ACTOR_NAME AVG_THROUGHPUT MAX_THROUGHPUT
------------ ------------- --------------
actor2 1 33
actor3 0 0
actor1 2 53

How do I convert the above out put to following format.

EV_ACTOR_NAME value1 value2
-------------- -------------- --------------
actor2 AVG 1
actor2 MAX 33
actor3 AVG 0
actor3 MAX 0
actor1 AVG 2
actor1 MAX 55

I tried the following

WITH data AS
(select level a from dual connect by level <= 3)
select EE_ACTOR_NAME,decode(a, 1, AVG_ThroughPut, 2, MAX_Throughput) value from
(

[Code]...

but i always get the following error ORA-00904: "A": invalid identifier

View 5 Replies View Related

SQL & PL/SQL :: Record Event If No Rows Are Returned Between Two Timestamp?

Feb 21, 2013

I have employees under a supervisor defines as below:

select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'

Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:

select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1),
case when CLOCK_OUT_DATE is null then
'Not clocked out'
else

[code]....

The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.

How can we modify query to show those 26 people as 'Not clocked In'

View 7 Replies View Related

SQL & PL/SQL :: Date Conversion

Jan 12, 2012

I have an issue with date conversion. In my table dates are in this formate:

04-DEC-90 10:46:46

I need to convert it in To_date.

SQL> select to_date('04-DEC-90','dd-mon-yy') from dual;

TO_DATE('04-DEC-90','DD-MON-YY
------------------------------
04/12/2090

SQL>

But the year shoul be 1990 not 2090.

View 3 Replies View Related

How To Get Shorter Date Format (without Timestamp)

Sep 11, 2009

I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.

This is what I get: 2009-08-04 00:00:00

This is what I want: 2009-08-04

View 3 Replies View Related

Convert Date Into UNIX Timestamp

Dec 17, 2008

how to convert date into UNIX time in oracle ?

View 1 Replies View Related

SQL & PL/SQL :: How To Get Date From The Timestamp Data Type

Oct 13, 2011

How can i get just date from the timestamp data type.

Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.

View 6 Replies View Related

SQL & PL/SQL :: Getting Date Portion Of Timestamp Field?

May 19, 2011

I have a timestamp field in an oracle table. The data in that field looks like this.

19-MAY-2011 10.55.21.628206000 AM

I want to query the data in this field by only date portion (not the time portion).

Something like this.

Select * from mytable where archivedate = to_date('19-may-2011','dd-mon-yyyy')

this query doesn't return any data. But actually there is data for 19-may-2011 (along with time portion) in that field.

how to query based on only date portion?

View 4 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

Epoch Date Conversion

May 31, 2011

My company uses Remedy which stores date/time as an integer. I use the following formula to convert it to a readable date:

TO_CHAR(TO_DATE('01/01/1970', 'MM/DD/YYYY HH:MI:SS AM')+ ((createdate - 4*3600)/(60*60*24)),'MM/DD/YYYY HH:MI:SS AM') as Create_Date

I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.

View 3 Replies View Related

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

View 6 Replies View Related

SQL & PL/SQL :: Date To Number Conversion

May 6, 2010

I want to change a table datatype from date to number where already existing data should get convert.any possibility of doing where i tried like this but no get changing. Even as Julian format is working a bit i want the data to come as GMT format

Scenarios where i tried are like this.

ALTER TABLE Contacts ADD ALERT_DATE1 NUMBER(20,0)
/
UPDATE Contacts SET ALERT_DATE1 = TO_NUMBER(ALERT_DATE)
/
ALTER TABLE Contacts DROP COLUMN ALERT_DATE
/
ALTER TABLE Contacts RENAME COLUMN ALERT_DATE1 TO ALERT_DATE
/

but second statement failing.

Julian fomat like

SELECT sysdate,
TO_CHAR(sysdate, 'J'),
TO_DATE(TO_CHAR(sysdate, 'J'),'J')
FROM dual;

View 10 Replies View Related

SQL & PL/SQL :: Date / Time Conversion

Apr 4, 2010

I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:

some columns contain times in the format : "00:00:00" eg..... "15:00:00"
some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000"
some columns have time and date eg.. "05/09/2000 15:49:39"

Currently I have the data held in tables within an access database, and in CSV format.

eg, I have dates like 03/04/2010 which i need to be 03-APR-10....

how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question

View 21 Replies View Related

PL/SQL :: String To Date Conversion

Jun 25, 2012

I recently became involved with databases, and i've came across with a little obstacle. I have strings that represent a date, they are very oddly formatted and need to store them as dates. the string format looks like this: 'Monday, May the 13th of 2001'

How can i effectively convert them to DATE type?

View 7 Replies View Related

SQL & PL/SQL :: Separating Time And Date Form TimeStamp

Oct 19, 2010

How can i separate the Date and time into two separate fields from the Oracle TIMESTAMP?

View 1 Replies View Related

PL/SQL :: Logic To Compare Date With Standard Timestamp

Feb 3, 2013

I want to retrieve the data which is "n" moths old.

To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.

The condition should be as below.

UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)

UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);

View 6 Replies View Related

SQL & PL/SQL :: Conversion Of Julian Date Into Dd / Mm / Yyyy

Mar 8, 2010

I have been given some data in excel sheet to be uploaded in an Oracle Table. The dates are in Julian. The date in Julian in excel sheet is as :-'110048'.

In the excel file, I found that the cell was formatted as General and when I changed the formatting to Date I got the result as '19/04/2211'.

tell me a way to convert this Julian to mm/dd/yyyy format to be inserted into a table in Oracle.

Tried this :-

SQL> SELECT to_char(to_date(to_char(110048), 'J'),'DD/MM/YYYY') FROM dual;

TO_CHAR(TO
----------
18/04/4411

Not sure how to go about it.

View 5 Replies View Related

SQL & PL/SQL :: Oracle Date Conversion Errors

Mar 14, 2011

I am having a strange issue with date conversion. The errors encountered are :

PLS-00382: expression is of wrong type
PLS-00306: wrong number or types of arguments in call to '>'

The situation or test case is as follows:

create table sand_programs(prog_code varchar2(20), prog_end_dt date);

create table sand_program1(prog_code varchar2(20), filing_date number(15));
Result : Both the tables are created.

Now I create a procedure below as mentioned, to check if the filing_date is greater than the prog_end_dt or not.If the filing_date is greater than prog_ end_dt, then it should go to the 1st "dbms_output.put_line" message else it should go to the 2nd "dbms_output.put_line" message.

Here's my procedure:

create or replace procedure test_sand(p_program_cd in number) is
v_prog_end_dt date;
v_filing_date number;
begin
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Date Conversion From Unix To Oracle

Jun 14, 2010

I have a UNIX shell script as following,

#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........

I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.

rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37

The oracle Procedure's SQL is as following,

SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........

The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..

Do I need to convert the Date ? ( But i_last_update_datetime is DATE)

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved