ansaurus

Question

Coalesce in Oracle

Answer 1

A:

This documentation is very clear on explaining how we use COALESCE.

jpartogi 2010-03-03 12:41:13

Answer 2

+2 A:

coalesce is a function that takes two (or more) parameters and return the first that is not null.

Given a table with a nullable column called, say, Age, you could write the following:

select coalesce(Age, 18) from MyTable;

klausbyskov 2010-03-03 12:42:47

what's the difference between this use of the function coalesce and the NVL function?Thanks - Jay

Jay 2010-03-03 14:17:12

@Jay: `COALESCE()` is portable -- it's part of the ANSI standard.

Adam Musch 2010-03-03 14:21:43

@Jay - besides what Adam says `COALESCE()` takes any number of parameters whereas `NVL()` takes only the two.

APC 2010-03-03 16:37:47

The most important difference, imho, is COALESCE is short-circuited and NVL is not.

David 2010-03-03 18:12:11

Answer 3

+3 A:

I originally suspected that you were asking about coalescing tablespaces:

alter tablespace mytablespace coalesce;

This combines contiguous extents into larger extents. See Oracle 10G Docs

But now I think perhaps what you are looking for is

alter table mytable shrink space compact;

Thiis described in detail in this Oracle Magazine article.

Tony Andrews 2010-03-03 12:56:36

I do not want to coalesce the whole tablespace, i just need to coalesce one table only.

2010-03-04 05:19:31

The comments here may help: http://p2p.wrox.com/oracle/19758-oracle-8i-alter-table-table-coalesce-partition.html

Jeffrey Kemp 2010-03-04 05:54:12

@usernnn, see my revised answer. Is that what you are looking for?

Tony Andrews 2010-03-04 10:05:27

Answer 4

A:

If I understand what you're trying to do, you'll need to:

ALTER TABLE blah MOVE TABLESPACE different-tablespace;

then move it back:

ALTER TABLE blah MOVE TABLESPACE original-tablespace;

cagcowboy 2010-03-03 13:37:02

Coalesce in Oracle