Some things on Oracle proved to be very useful for me. I’ll share them below because I’m sure they are useful for some of you too.
1. Searching for a column name
select * from all_tab_columns where table_name like ‘C%’ and column_name like ‘%ST%’
2. Add/delete a column
alter table account add account_number varchar2(30)
alter table account add (account_number varchar2(30), balance decimal(20,4))
alter table account drop column account_number
3. Change column name / data type
alter table account rename column account_no to account_number
alter table account modify account varchar2(35)
4. Change table name
alter table account rename to customer_account
5. Update a column based on another table
update account set account_number =
( select account_number
from account_mapping
where account_mapping.customer_id = account.customer_id
) where account_number is null
6. Create a table from another table
(this is very useful for backing up before updating)
create table account_bak as
select * from account where 1=2
7. Escaping & (ampersand)
update table1 set column1 = ‘M’ || chr(38) || ‘S’
Good day, Vincent!
I think (or hope), these two examples will be useful for You
——————————————————————————————-
WITH t AS
(
SELECT id_contract
,COUNT (*) AS cnt
,SUM (COUNT (*)) OVER (ORDER BY id_contract) AS running_cnt
FROM contract_balance
GROUP BY id_contract
)
SELECT MIN (id_contract) AS from_id
,MAX (id_contract) AS to_id
,SUM (cnt) AS cnt
FROM t
GROUP BY TRUNC(running_cnt/1e7)
ORDER BY 1 ;
SELECT a.range_number
,MIN(id_contract) AS min_id_contract
,MAX(id_contract) AS max_id_contract
,COUNT(1) AS rows_count
FROM (
SELECT id_contract, NTILE(8) OVER (ORDER BY id_contract) AS range_number
FROM contract_balance
) a
GROUP BY a.range_number ;
——————————————————————————————-
Table contract_balance is Periodic Snapshot.
Table contract_balance contains 200 million records.
This sql queries tested on Oracle 9.2, 10.2g
Comment by Alexander — 24 December 2012 @ 5:56 am |
Thank you Alexander. Yes it is useful. Merry Christmas.
Comment by Vincent Rainardi — 25 December 2012 @ 2:30 pm |