Handy stuff when you work with oracle Database

When a database is so big and you do not now where to start, here are some helpfull selectstatements.

Let oracle search it for you.

Here are some usefull Selects:

• The select for finding all Stored Procedures where you use some specific text
  select * from all_source where Upper(text) like Upper('%YOUR_VALUE_TO_SEARCH%');
  

• Select statement where you use the column name in a table
  select * from all_tab_columns where column_name = Upper('YOUR_VALUE_TO_SEARCH');
  

• Selectstatement for finding the indexes use on some Column name
  select * from all_ind_columns where column_name = Upper('YOUR_VALUE');
  

• Select statement for finding constraints use on some column name
  select * from all_cons_columns where column_name = Upper('YOUR_VALUE');
  

Performace Tips:

• Find the record count , the fastest way
  Select Count(0) from TABLENAME;

• Left join is slower against Inner join.

Reason:
When you use * it uses a varchar, when you use a number is it faster. there is no cast form varchar to Integer

• Update TableName set Columname = 1 where CMD_ID in (Select CMD_ID from TableName)
  the updatestatement above works but slow, use this instead:
  Update TableName1 set Columname = 1
  where exists (
  Select 1 from TableName2
  where TableName1.CMDID = TableName2.CMDID
  )

• Write a select statement that works faster
  Select id, name, firstname, function
  from functionlist FL
  inner join person P on P.personid = FL.id
  inner join functions F on F.functionid = FL.idfunction

• If table contains something like
  Select 1 from TableName where rownum < 2
  The database stops searching when it finds the first item.

Oracle Version

• select * from v$version
• select * from product_component_version;